package org.trajectory.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.pentaho.commons.connection.IPentahoConnection;
import org.pentaho.commons.connection.IPentahoResultSet;
import org.springframework.beans.factory.annotation.Autowired;
import org.trajectory.data.Shop;

public class ShopDao extends BaseDao {
	public ConnectionService connectionService;

	@Autowired
	public void setConnectionService(ConnectionService connectionService) {
		this.connectionService = connectionService;
	}

	private IPentahoConnection getConnection(String datasource) {
		return this.connectionService.getConnection(datasource);
	}

	public List<Shop> loadShops(String datasource) throws Exception {
		List<Shop> list = new ArrayList<Shop>();
		IPentahoConnection conn = null;
		IPentahoResultSet rs = null;
		try{
			conn = getConnection(datasource);
			rs = conn.executeQuery("select id , shop_id , shop_name , floorplan from shop order by shop_id;");
			Object[] obj;
			while ((obj = rs.next()) != null) {
				Shop shop = new Shop();
				shop.setShopId(Integer.valueOf(obj[1].toString()));
				shop.setShopName(obj[2].toString());
				shop.setFloorplan((byte[]) obj[3]);
				list.add(shop);
			}
		}finally{
			if(rs != null)
				rs.close();
			if(conn != null)
				conn.close();
		}
		return list;
	}

	public Shop loadShop(String datasource, int shopId) throws Exception {
		Shop shop = new Shop();
		IPentahoConnection conn =null;
		IPentahoResultSet rs = null;
		try{
			conn = getConnection(datasource);
			rs = conn.executeQuery("select id , shop_id , shop_name , floorplan from shop where shop_id = " + shopId + ";");
			Object[] obj;
			if ((obj = rs.next()) != null) {
				shop.setShopId(Integer.valueOf(obj[1].toString()));
				shop.setShopName(obj[2].toString());
				shop.setFloorplan((byte[]) obj[3]);
			}
		}finally{
			if(rs != null)
				rs.close();
			if(conn != null)
				conn.close();
		}
		return shop;
	}

	public void updateShop() throws InstantiationException,
			IllegalAccessException, ClassNotFoundException, SQLException,
			IOException {
		try {
			List<Object[]> data = new ArrayList<Object[]>();
			Object[] obj = new Object[3];
			File f = new File(
					"D:/dev/Fusion/Floorplan_and_product_information/Drkong.jpg");
			obj[0] = 0;
			obj[1] = 0;
			obj[2] = getBytesFromFile(f);
			data.add(obj);
			conn.connect();
			conn
					.executeBatch(
							"insert into shop (shop_id , shop_name , floorplan) values(?, ? , ?)",
							data);
		} finally {
			if (conn != null)
				conn.close();
		}
	}

	public static Connection getConnection() throws Exception {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://158.182.150.115/shoes";
		String username = "root";
		String password = "icmdb2010";

		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url, username, password);
		return conn;
	}

	public static void main(String[] args) throws SQLException, IOException {
		String fileName = "D:/dev/Fusion/Floorplan_and_product_information/Drkong.jpg";

		FileInputStream fis = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			File file = new File(fileName);
			fis = new FileInputStream(file);
			pstmt = conn
					.prepareStatement("insert into shop (shop_id , shop_name , floorplan) values(?, ? , ?)");
			pstmt.setInt(1, 0);
			pstmt.setInt(2, 0);
			pstmt.setAsciiStream(3, fis, (int) file.length());
			pstmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			System.err.println("Error: " + e.getMessage());
			e.printStackTrace();
		} finally {
			pstmt.close();
			fis.close();
			conn.close();
		}
	}

	public static byte[] getBytesFromFile(File file) throws IOException {
		InputStream is = new FileInputStream(file);

		// Get the size of the file
		long length = file.length();

		// You cannot create an array using a long type.
		// It needs to be an int type.
		// Before converting to an int type, check
		// to ensure that file is not larger than Integer.MAX_VALUE.
		if (length > Integer.MAX_VALUE) {
			// File is too large
		}

		// Create the byte array to hold the data
		byte[] bytes = new byte[(int) length];

		// Read in the bytes
		int offset = 0;
		int numRead = 0;
		while (offset < bytes.length
				&& (numRead = is.read(bytes, offset, bytes.length - offset)) >= 0) {
			offset += numRead;
		}

		// Ensure all the bytes have been read in
		if (offset < bytes.length) {
			throw new IOException("Could not completely read file "
					+ file.getName());
		}

		// Close the input stream and return bytes
		is.close();
		return bytes;
	}
}
